package com.hkts.naturalgas.dictionaries.utils;

import com.hkts.naturalgas.dictionaries.entity.HktsEnvironmentProtectionDictionaries;
import com.hkts.naturalgas.dictionaries.entity.HktsEnvironmentProtectionItem;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassNameDBUtils
 * @Description
 * @Author yck
 * @Date2021-06-23 16:24
 * @Version V1.0
 **/
@Component
@Slf4j
public class DBUtils {
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    public List<Map<String, Object>> excuteQuerySql(String sql){
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        PreparedStatement pst = null;
        SqlSession session = getSqlSession();
        ResultSet result = null;
        try {
            pst = session.getConnection().prepareStatement(sql);
            result = pst.executeQuery();
            ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (result.next()) {
                Map<String,Object> rowData = new HashMap<String,Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), result.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }
        return list;
    }

    /**
     * 获取sqlSession
     * @return
     */
    public SqlSession getSqlSession(){
        return SqlSessionUtils.getSqlSession(sqlSessionTemplate.getSqlSessionFactory(),
                sqlSessionTemplate.getExecutorType(), sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    /**
     * 关闭sqlSession
     * @param session
     */
    public void closeSqlSession(SqlSession session){
        SqlSessionUtils.closeSqlSession(session, sqlSessionTemplate.getSqlSessionFactory());
    }
   /** 功能描述:
   *
   * @return:
   * @Author: 杨春科
   * @Date: 2021-06-23 16:42
   * @Description //TODO  获取数据
   */
   public List<Map<String, Object>> getList(HktsEnvironmentProtectionDictionaries hktsEnvironmentProtectionDictionaries,List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems,String date,String section) throws ParseException {
       StringBuffer buffer=new StringBuffer();
       buffer.append("select");
       for(int i=0;i<hktsEnvironmentProtectionItems.size();i++){
           buffer.append(" ");
           if(i==0){
               buffer.append(hktsEnvironmentProtectionItems.get(i).getFieldName());
           }else {
               buffer.append(","+hktsEnvironmentProtectionItems.get(i).getFieldName());
           }
       }
       buffer.append(" from ").append(hktsEnvironmentProtectionDictionaries.getTableName());
       buffer.append(" where ");
       String[] dateRange = getDateRange(date,section);
       buffer.append("time >='"+dateRange[0]+"' and time <= '"+dateRange[1]+"'");
       List<Map<String, Object>> maps = excuteQuerySql(buffer.toString());
       return maps;
   }
   /** 功能描述:
   *
   * @return:
   * @Author: 杨春科
   * @Date: 2021-06-23 17:11
   * @Description //TODO 计算开始时间 结束时间
   */
    public String[] getDateRange(String date,String section) throws ParseException {
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat format2=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int startTime=0;
        int endTime=0;
        if(section.equals("1")){
            startTime=480;
            endTime=1200;
        }else {
            startTime=1200;
            endTime=1680;
        }
        Date parse = format.parse(date);
        Calendar c = new GregorianCalendar();
        c.setTime(parse);
        c.add(Calendar.MINUTE, startTime);
        Date time = c.getTime();
        String format1 = format2.format(time);
        Calendar c2 = new GregorianCalendar();
        c2.setTime(parse);
        c2.add(Calendar.MINUTE, endTime);
        Date time2 = c2.getTime();
        String format3 = format2.format(time2);
        String[] arr={format1,format3};
        return arr;
    }
}
